﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Estimate.Common.Entity;

namespace Estimate.Common.Repository
{
    /// <summary>
    /// 台站隶属关系管理类
    /// </summary>
    public class AffiliationRepository : BaseRepository
    {
        public AffiliationRepository(string connectionString)
            : base(connectionString, "Affiliation")
        { }

        public override string GetCreateTableSql()
        {
            return "CREATE TABLE [dbo].[Affiliation](	[id] [int] IDENTITY(1,1) NOT NULL,	[name] [nvarchar](50) NOT NULL,	[code] [nvarchar](5) NOT NULL,	[parent_id] [int] NULL,	[level] [int] NOT NULL,	[lontitude] [nvarchar](6) NOT NULL,	[latitude] [nvarchar](5) NOT NULL,	[height] [nvarchar](6) NOT NULL,PRIMARY KEY CLUSTERED  (	[id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]        ";        
        }

        /// <summary>
        /// 给定城市名称，获取该市下属各台站的站名及对应站点号
        /// </summary>
        /// <param name="cityname">城市名称</param>
        /// <returns>以城市名为字典，站点号为值的字典</returns>
        public Dictionary<string, string> GetAreaSubStationsByName(string cityname)
        {
            string sql = string.Format("SELECT a.name ,a.code   FROM {0} a, {0} b WHERE a.parent_id = b.id and b.name = @name and b.level=5", _tableName);
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("name", cityname);

            Dictionary<string, string> result = new Dictionary<string, string>();

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql,parameters);
            
            while (reader.Read())
            {
                result[reader["name"].ToString()] = reader["code"].ToString();
            }
            connection.Close();
            return result;
        }

        /// <summary>
        /// 给定城市编码，获取该市下属各台站的站名及对应站点号
        /// </summary>
        /// <param name="citycode">城市代码</param>
        /// <returns>以城市名为字典，站点号为值的字典</returns>
        public Dictionary<string, string> GetAreaSubStationsByCode(string citycode)
        {
            string sql = string.Format("SELECT a.name ,a.code   FROM {0} a, {0} b WHERE a.parent_id = b.id and b.code = @code and b.level=5", _tableName);
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("code", citycode);

            Dictionary<string, string> result = new Dictionary<string, string>();

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, parameters);

            while (reader.Read())
            {
                result[reader["name"].ToString()] = reader["code"].ToString();
            }
            connection.Close();
            return result;
        }

        /// <summary>
        /// 给定城市编码，获取该市下属各台站的站名及对应站点号
        /// </summary>
        /// <param name="citycode">城市代码</param>
        /// <returns>下属站点的站号</returns>
        public string GetAreaSubStationsString(string citycode)
        {
            CacheClient.KeyPrefix = this._tableName + "GetAreaSubStationsString";
            string cacheKey = citycode;
            object cache = CacheClient.Get(cacheKey);
            if (cache == null)
            {
                string sql = string.Format("SELECT a.name ,a.code   FROM {0} a, {0} b WHERE a.parent_id = b.id and b.code = @code and b.level=5", _tableName);
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter("code", citycode);

                List<string> result = new List<string>();


                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, parameters);

                while (reader.Read())
                {
                    result.Add(reader["code"].ToString());
                }
                connection.Close();
                string value = string.Join(",", result.ToArray());
                CacheClient.Set(cacheKey, value);
                return value;
            }
            else
            {
                string result = cache as string;
                return result;
            }
        }


        /// <summary>
        /// 给定子站code,获取父级站的code
        /// </summary>
        /// <param name="citycode">子站点的code</param>
        /// <returns>父级站点code</returns>
        public string GetParentStationCode(string citycode)
        {
            string sql = string.Format("SELECT b.code   FROM {0} a, {0} b WHERE a.parent_id = b.id and a.code = @code and b.level=5", _tableName);
            SqlParameter[] parameters = new SqlParameter[1];
            parameters[0] = new SqlParameter("code", citycode);

            string result = "";

            SqlConnection connection = new SqlConnection(_connectionString);
            SqlDataReader reader = DBHelper.ExecuteReader(connection, sql, parameters);

            if (reader.Read())
            {
                result = reader["code"].ToString();
            }
            connection.Close();
            return result;
        }

        /// <summary>
        /// 获取所有的县级站点
        /// </summary>
        /// <returns></returns>
        public Dictionary<string,string> GetAllStations()
        {
            Dictionary<string, string> result = new Dictionary<string, string>();
            AffiliationEntity[] entities = this.GetStationLevel(4);
            foreach (AffiliationEntity entity in entities)
            {
                result[entity.name] = entity.code;
            }
            return result;
        }

        public AffiliationEntity[] GetStationLevel(int level)
        {
            CacheClient.KeyPrefix = this._tableName + "GetStationLevel" + level.ToString();
            string cacheKey = "GetStationLevel4";
            object cache = CacheClient.Get(cacheKey);
            if (cache == null)
            {
                string sql = string.Format("SELECT * FROM {0}  WHERE level= {1}", _tableName,level);

                List<AffiliationEntity> result = new List<AffiliationEntity>();

                SqlConnection connection = new SqlConnection(_connectionString);
                SqlDataReader reader = DBHelper.ExecuteReader(connection, sql);

                while (reader.Read())
                {
                    AffiliationEntity entity = new AffiliationEntity();
                    entity.name = reader["name"].ToString();
                    entity.code = reader["code"].ToString();
                    entity.parent_id = Convert.ToInt32(reader["parent_id"].ToString());
                    entity.level = 4;
                    if (reader["lontitude"].ToString() == "")
                        entity.lontitude = 0;
                    else
                        entity.lontitude = Convert.ToSingle(reader["lontitude"].ToString());

                    if (reader["latitude"].ToString() == "")
                        entity.latitude = 0;
                    else
                        entity.latitude = Convert.ToSingle(reader["latitude"].ToString());

                    if (reader["height"].ToString() == "")
                        entity.height = 0;
                    else
                        entity.height = Convert.ToSingle(reader["height"].ToString());
                    result.Add(entity);
                }
                connection.Close();
                CacheClient.Set(cacheKey, result.ToArray());
                return result.ToArray();
            }
            else
            {
                AffiliationEntity[] result = cache as AffiliationEntity[];
                return result;
            }
        }
    }
}
